package com.xiong.excel;

import com.xiong.excel.ext.CellCallbackExt;
import com.xiong.excel.ext.CellValueCallbackExt;
import com.xiong.excel.ext.RowCallbackExt;
import com.xiong.excel.ext.RowCellValueCallbackExt;
import com.xiong.excel.extmodel.CellValueModel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

public class ExcelUtils {



    public static void main(String[] args) {
        try (FileInputStream fileInputStream = new FileInputStream("C:\\Users\\86186\\Desktop\\新建文件夹\\新建 Microsoft Excel 工作表.xlsx")) {
            // 打开现有的Excel文件
            Workbook workbook = new XSSFWorkbook(fileInputStream);

            // 选择要修改的工作表
            Sheet sheet = workbook.getSheet("Sheet1");

            // 获取指定行的数据
            Row row = sheet.getRow(0);
            int num=0;
            // 遍历行中的每一列
            for (Cell cell : row) {
                // 获取单元格的值
                CellType cellType = cell.getCellType();
                String cellValue = "";
                if (cellType == CellType.STRING) {
                    cellValue = cell.getStringCellValue();
                } else if (cellType == CellType.NUMERIC) {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }

                if(num==6){

                    // 获取指定单元格的样式
                    CellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中

                    // 创建字体对象，并设置为加粗
                    Font font = workbook.createFont();
                    font.setBold(true);

                    // 将字体应用于样式
                    cellStyle.setFont(font);

                    cell.setCellValue("修改的值");
                    cell.setCellStyle(cellStyle);
                }
                // 打印单元格的值
                System.out.println("Cell Value: " + cellValue+num);
                num++;
            }

            Row row2 = sheet.getRow(2);
            for (Cell cell : row2) {
                System.out.println(cell.getStringCellValue());
            }

            // 保存修改后的Excel文件
            try (FileOutputStream outputStream = new FileOutputStream("C:\\Users\\86186\\Desktop\\新建文件夹\\新建 Microsoft Excel 工作表222.xlsx")) {
                workbook.write(outputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static List<Map<String,Object>> getList(File file){
        return getList(file,0,0);
    }

    public static void wirteTemplate(File oldFile,File newFile,int sheetIndex,RowCallbackExt rowCallbackExt){
        FileInputStream fileInputStream = null;
        try {
            fileInputStream=new FileInputStream(oldFile);
            // 打开现有的Excel文件
            Workbook workbook = null;
            String fileName=oldFile.getName();
            if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(fileInputStream);
            } else if (fileName.endsWith(".xls") || fileName.endsWith(".et")) {
                workbook = new HSSFWorkbook(fileInputStream);
            }
            // 选择要修改的工作表
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            // 获取行
            Iterator<Row> rows = sheet.rowIterator();

            Row row;
            int rowIndex=0;
            while (rows.hasNext()) {
                row = rows.next();
                rowCallbackExt.handler(row,rowIndex);
                rowIndex++;
            }
            // 保存修改后的Excel文件
            try (FileOutputStream outputStream = new FileOutputStream(newFile)) {
                workbook.write(outputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static void wirteTemplate(File oldFile, File newFile, int sheetIndex, CellCallbackExt callbackExt){
        FileInputStream fileInputStream = null;
        try {
            fileInputStream=new FileInputStream(oldFile);
            // 打开现有的Excel文件
            Workbook workbook = null;
            String fileName=oldFile.getName();
            if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(fileInputStream);
            } else if (fileName.endsWith(".xls") || fileName.endsWith(".et")) {
                workbook = new HSSFWorkbook(fileInputStream);
            }
            // 选择要修改的工作表
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            // 获取行
            Iterator<Row> rows = sheet.rowIterator();

            Row row;
            Cell cell;
            int rowIndex=0;
            Map<String,Object> map=null;
            while (rows.hasNext()) {
                row = rows.next();
                // 获取单元格
                Iterator<Cell> cells = row.cellIterator();
                int cellIndex=0;
                while (cells.hasNext()) {
                    cell = cells.next();
                    callbackExt.handler(cell,rowIndex,cellIndex);
                    cellIndex++;
                }
                rowIndex++;
            }
            // 保存修改后的Excel文件
            try (FileOutputStream outputStream = new FileOutputStream(newFile)) {
                workbook.write(outputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static List<Map<String,Object>> getList(File file,int sheetIndex,int titleRow){
        FileInputStream fileInputStream = null;
        try {
            fileInputStream=new FileInputStream(file);
            // 打开现有的Excel文件
            Workbook workbook = null;
            String fileName=file.getName();
            if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(fileInputStream);
            } else if (fileName.endsWith(".xls") || fileName.endsWith(".et")) {
                workbook = new HSSFWorkbook(fileInputStream);
            }
            // 选择要修改的工作表
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            // 获取行
            Iterator<Row> rows = sheet.rowIterator();

            Map<String,String> headMap=new HashMap<>();

            Row row;
            Cell cell;
            int rowIndex=0;
            List<Map<String,Object>> mapList=new ArrayList<>();
            Map<String,Object> map=null;
            while (rows.hasNext()) {
                row = rows.next();
                // 获取单元格
                Iterator<Cell> cells = row.cellIterator();
                int cellIndex=0;
                if(rowIndex > titleRow){
                    map=new HashMap<>();
                }
                while (cells.hasNext()) {
                    cell = cells.next();
                    String cellValue = "";
                    if (cell != null) {
                        switch (cell.getCellType()) {
                            case STRING:
                                cellValue = cell.getStringCellValue();
                                break;
                            case NUMERIC:
                                cellValue = String.valueOf(cell.getNumericCellValue());
                                break;
                            case BOOLEAN:
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case FORMULA:
                                cellValue = cell.getCellFormula();
                                break;
                            default:
                                cellValue = "";
                        }
                        if(rowIndex == titleRow){
                            headMap.put(String.valueOf(cellIndex),cellValue);
                        }else if(rowIndex > titleRow){
                            String title=headMap.get(String.valueOf(cellIndex));
                            map.put(title,cellValue);
                        }
                    }
                    cellIndex++;
                }
                if(rowIndex > titleRow){
                    mapList.add(map);
                }
                rowIndex++;
            }
            return mapList;
        }catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void createExcel(List<Map<String,Object>> dataList, String filePath, RowCellValueCallbackExt rowCellValueCallbackExt){
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");
        // 创建表头
        createHeader(sheet, dataList.get(0));

        // 填充数据
        int rowNum = 1;
        for (Map<String, Object> data : dataList) {
            Row row = sheet.createRow(rowNum++);
            int colNum = 0;
            Map<String,CellValueModel> modelMap=new HashMap<>();
            for (String key : data.keySet()) {
                Cell cell = row.createCell(colNum++);
                Object value = data.get(key);
                setCellValue(cell, value);
                CellValueModel cellValueModel=new CellValueModel(cell,workbook,key,value);
                modelMap.put(key,cellValueModel);
            }
            rowCellValueCallbackExt.handler(rowNum,modelMap);
        }
        // 自动调整列宽
        autoSizeColumns(sheet);

        // 保存Excel文件
        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            workbook.write(outputStream);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                workbook.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }


    public static void createExcel(List<Map<String,Object>> dataList, String filePath, CellValueCallbackExt cellValueCallbackExt){
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");
        // 创建表头
        createHeader(sheet, dataList.get(0));

        // 填充数据
        int rowNum = 1;
        for (Map<String, Object> data : dataList) {
            Row row = sheet.createRow(rowNum++);
            int colNum = 0;
            for (String key : data.keySet()) {
                Cell cell = row.createCell(colNum++);
                Object value = data.get(key);
                setCellValue(cell, value);
                CellValueModel cellValueModel=new CellValueModel(cell,workbook,key,value);
                cellValueCallbackExt.handler(cellValueModel);
            }
        }
        // 自动调整列宽
        autoSizeColumns(sheet);

        // 保存Excel文件
        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            workbook.write(outputStream);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                workbook.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    public static void createExcel(List<Map<String, Object>> dataList, String filePath){
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");
        // 创建表头
        createHeader(sheet, dataList.get(0));

        // 填充数据
        int rowNum = 1;
        for (Map<String, Object> data : dataList) {
            Row row = sheet.createRow(rowNum++);
            int colNum = 0;
            for (String key : data.keySet()) {
                Cell cell = row.createCell(colNum++);
                Object value = data.get(key);
                setCellValue(cell, value);
            }
        }
        // 自动调整列宽
        autoSizeColumns(sheet);

        // 保存Excel文件
        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            workbook.write(outputStream);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                workbook.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    private static void setCellValue(Cell cell, Object value) {
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        }
        // 根据需要，您可以添加其他类型的数值处理
    }

    private static void createHeader(Sheet sheet, Map<String, Object> data) {
        Row headerRow = sheet.createRow(0);
        int colNum = 0;
        for (String key : data.keySet()) {
            Cell cell = headerRow.createCell(colNum++);
            cell.setCellValue(key);
        }
    }


    private static void autoSizeColumns(Sheet sheet) {
        int numColumns = sheet.getRow(0).getPhysicalNumberOfCells();
        for (int colIndex = 0; colIndex < numColumns; colIndex++) {
            sheet.autoSizeColumn(colIndex);
        }
    }



}
